package com.ycxy.crm.commons.utils;

import com.ycxy.crm.workbench.domain.Activity;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * @Author jayLuo
 * @Date 2022/9/29 15:22
 * @PackageName:com.ycxy.crm.commons.utils
 * @ClassName: HSSFUtils
 * @Description: 关于Excel文件操作的工具类
 * @Version 1.0
 */
public class HSSFUtils {
    /**
     * 从指定的HSSFCell对象中获取列的值
     * @return
     */
    public static String getCellValueForStr(HSSFCell cell){
        String ret ="";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                ret = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                ret = cell.getNumericCellValue()+"";
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                ret = cell.getBooleanCellValue()+"";
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                ret = cell.getCellFormula();
                break;
            default:
                ret = "";
                break;
        }
        return ret;
    }

    /**
     * 将市场活动导出到Excel文件
     */
    public static void exportActivities(HttpServletResponse response, List<Activity> activityList, String sheetName) throws IOException {
        //创建excel文件,并把activityList写入到excel文件中
        //创建HSSFWorkbook对象对应一个excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //使用wb创建HSSFSheet对象,对应wb文件中的一页
        HSSFSheet sheet = wb.createSheet(sheetName);
        //使用sheet创建HSSRow对象,对应sheet中的一行
        HSSFRow row = sheet.createRow(0);//行号:从0开始,依次增加
        //使用row对象HSSFCell对象,对应row中的列
        HSSFCell cell = row.createCell(0);//列号:从0开始,依次增加
        cell.setCellValue("ID");
        cell = row.createCell(1);//列号:从0开始,依次增加
        cell.setCellValue("所有者");
        cell = row.createCell(2);//列号:从0开始,依次增加
        cell.setCellValue("名称");
        cell = row.createCell(3);//列号:从0开始,依次增加
        cell.setCellValue("开始日期");
        cell = row.createCell(4);//列号:从0开始,依次增加
        cell.setCellValue("结束日期");
        cell = row.createCell(5);//列号:从0开始,依次增加
        cell.setCellValue("成本");
        cell = row.createCell(6);//列号:从0开始,依次增加
        cell.setCellValue("描述");
        cell = row.createCell(7);//列号:从0开始,依次增加
        cell.setCellValue("创建时间");
        cell = row.createCell(8);//列号:从0开始,依次增加
        cell.setCellValue("创建者");
        cell = row.createCell(9);//列号:从0开始,依次增加
        cell.setCellValue("修改时间");
        cell = row.createCell(10);//列号:从0开始,依次增加
        cell.setCellValue("修改者");

        //遍历activityList,创建HSSFRow对象,生成所有的数据行
        if (activityList != null && activityList.size()>0) {
            Activity activity = null;
            for (int i = 0; i < activityList.size(); i++) {
                activity = activityList.get(i);
                //每遍历出一个activity对象生成一行
                row = sheet.createRow(i+1);
                //每一行创建11列,每一列数据从activityList中取
                cell = row.createCell(0);
                cell.setCellValue(activity.getId());
                cell = row.createCell(1);
                cell.setCellValue(activity.getOwner());
                cell = row.createCell(2);
                cell.setCellValue(activity.getName());
                cell = row.createCell(3);
                cell.setCellValue(activity.getStartDate());
                cell = row.createCell(4);
                cell.setCellValue(activity.getEndDate());
                cell = row.createCell(5);
                cell.setCellValue(activity.getCost());
                cell = row.createCell(6);
                cell.setCellValue(activity.getDescription());
                cell = row.createCell(7);
                cell.setCellValue(activity.getCreateTime());
                cell = row.createCell(8);
                cell.setCellValue(activity.getCreateBy());
                cell = row.createCell(9);
                cell.setCellValue(activity.getEditTime());
                cell = row.createCell(10);
                cell.setCellValue(activity.getEditBy());
            }
        }
       /* //调用工具函数生成excel文件
        OutputStream os = new FileOutputStream("D:\\Users\\one piece\\IdeaProjects\\crm\\activityList.xls");
        wb.write(os);*/

        //关闭资源
       /* os.close();
        wb.close();*/

        //把生成的excel文件下载到客户端
        //1.设置响应类型
        response.setContentType("application/octet-stream;charset=UTF-8");//设置返回一个二进制文件
        //可以设置响应头信息,使浏览器接收到响应信息之后,直接激活文件下载窗口,即使能打开也打不开
        response.addHeader("Content-Disposition","attachment;filename=activityList.xls");
        //2.获取输出流
        OutputStream out = response.getOutputStream();

        //浏览器接收到响应信息之后,默认情况,直接在显示窗口打开响应信息;即使打不开,也会调用应用程序打开;只有实在打不开,才会激活文件下载窗口

        //读取excel文件(InputStream),把文件输出到浏览器(outputStream)
        /*InputStream is = new FileInputStream("D:\\Users\\one piece\\IdeaProjects\\crm\\activityList.xls");
        byte[] buff = new byte[256];
        int len =0;
        while ((len=is.read(buff)) != -1){
            out.write(buff,0,len);
        }

        //关闭资源
        is.close();*/
        wb.write(out);

        wb.close();
        out.flush();
    }
}
